import express from 'express';
import sqlite3 from 'sqlite3';
import cors from 'cors';
import dotenv from 'dotenv';
import path from 'path';
import { fileURLToPath } from 'url';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

dotenv.config();

const app = express();
const PORT = process.env.PORT || 5173;

// 中间件
app.use(cors());
app.use(express.json());
app.use(express.static(path.join(__dirname, 'dist')));

// 数据库初始化
const db = new sqlite3.Database(process.env.DB_NAME || 'todolist.db');

// 创建表
db.serialize(() => {
  db.run(`CREATE TABLE IF NOT EXISTS ${process.env.DB_TABLE || 'todos'} (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT,
    completed BOOLEAN DEFAULT 0,
    priority TEXT DEFAULT 'medium',
    category TEXT DEFAULT 'general',
    due_date TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )`);
});

// API 路由

// 获取所有待办事项
app.get('/api/todos', (req, res) => {
  const { category, priority, completed } = req.query;
  let query = `SELECT * FROM ${process.env.DB_TABLE || 'todos'}`;
  const params = [];
  const conditions = [];

  if (category && category !== 'all') {
    conditions.push('category = ?');
    params.push(category);
  }

  if (priority && priority !== 'all') {
    conditions.push('priority = ?');
    params.push(priority);
  }

  if (completed !== undefined) {
    conditions.push('completed = ?');
    params.push(completed === 'true' ? 1 : 0);
  }

  if (conditions.length > 0) {
    query += ' WHERE ' + conditions.join(' AND ');
  }

  query += ' ORDER BY created_at DESC';

  db.all(query, params, (err, rows) => {
    if (err) {
      res.status(500).json({ error: err.message });
      return;
    }
    res.json(rows);
  });
});

// 创建新待办事项
app.post('/api/todos', (req, res) => {
  const { title, description, priority, category, due_date } = req.body;
  
  if (!title) {
    return res.status(400).json({ error: '标题不能为空' });
  }

  const query = `INSERT INTO ${process.env.DB_TABLE || 'todos'} 
                 (title, description, priority, category, due_date) 
                 VALUES (?, ?, ?, ?, ?)`;
  
  db.run(query, [title, description || '', priority || 'medium', category || 'general', due_date], function(err) {
    if (err) {
      res.status(500).json({ error: err.message });
      return;
    }
    res.json({ id: this.lastID, message: '待办事项创建成功' });
  });
});

// 更新待办事项
app.put('/api/todos/:id', (req, res) => {
  const { id } = req.params;
  const { title, description, completed, priority, category, due_date } = req.body;
  
  const query = `UPDATE ${process.env.DB_TABLE || 'todos'} 
                 SET title = ?, description = ?, completed = ?, priority = ?, category = ?, due_date = ?, updated_at = CURRENT_TIMESTAMP 
                 WHERE id = ?`;
  
  db.run(query, [title, description, completed ? 1 : 0, priority, category, due_date, id], function(err) {
    if (err) {
      res.status(500).json({ error: err.message });
      return;
    }
    if (this.changes === 0) {
      res.status(404).json({ error: '待办事项未找到' });
      return;
    }
    res.json({ message: '待办事项更新成功' });
  });
});

// 删除待办事项
app.delete('/api/todos/:id', (req, res) => {
  const { id } = req.params;
  
  const query = `DELETE FROM ${process.env.DB_TABLE || 'todos'} WHERE id = ?`;
  
  db.run(query, [id], function(err) {
    if (err) {
      res.status(500).json({ error: err.message });
      return;
    }
    if (this.changes === 0) {
      res.status(404).json({ error: '待办事项未找到' });
      return;
    }
    res.json({ message: '待办事项删除成功' });
  });
});

// 获取统计信息
app.get('/api/stats', (req, res) => {
  const queries = {
    total: `SELECT COUNT(*) as count FROM ${process.env.DB_TABLE || 'todos'}`,
    completed: `SELECT COUNT(*) as count FROM ${process.env.DB_TABLE || 'todos'} WHERE completed = 1`,
    pending: `SELECT COUNT(*) as count FROM ${process.env.DB_TABLE || 'todos'} WHERE completed = 0`,
    high_priority: `SELECT COUNT(*) as count FROM ${process.env.DB_TABLE || 'todos'} WHERE priority = 'high' AND completed = 0`
  };

  const stats = {};
  let completed = 0;
  const total = Object.keys(queries).length;

  Object.entries(queries).forEach(([key, query]) => {
    db.get(query, (err, row) => {
      if (err) {
        res.status(500).json({ error: err.message });
        return;
      }
      stats[key] = row.count;
      completed++;
      
      if (completed === total) {
        res.json(stats);
      }
    });
  });
});

// 服务静态文件
app.get('*', (req, res) => {
  res.sendFile(path.join(__dirname, 'dist', 'index.html'));
});

app.listen(PORT, '0.0.0.0', () => {
  console.log(`🚀 服务器运行在 http://localhost:${PORT}`);
  console.log(`📱 移动端访问: http://[你的IP]:${PORT}`);
});

// 优雅关闭
process.on('SIGINT', () => {
  console.log('\n正在关闭服务器...');
  db.close((err) => {
    if (err) {
      console.error('关闭数据库时出错:', err.message);
    } else {
      console.log('数据库连接已关闭');
    }
    process.exit(0);
  });
});